﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls; 
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using HZ.MBSM.DBUtility;

public partial class Agency_Compete : System.Web.UI.Page
{
    int CWProjectid;
    String StrSql;
    protected void Page_Load(object sender, EventArgs e)
    {
        System.Collections.Generic.List<HZ.MBSM.DAL.AccountType> _AccountType = new System.Collections.Generic.List<HZ.MBSM.DAL.AccountType>();
        _AccountType.Add(HZ.MBSM.DAL.AccountType.enumAttorney);
        if (HZ.MBSM.BLL.Login.CheckAssert(_AccountType))
        {
            
            //Db db = new Db();
            //System.Data.IDataReader dr;
            CWProjectid = SysFun.ToInt(SysFun.IsSQL(Request.QueryString["projectid"]));
            //StrSql = "Select count(*) cun From Project_Bid Where CompetitionSwich=1";
            //dr = db.DataReader(StrSql);
            //while (dr.Read())
            //{
            //    if (SysFun.ToInt(dr["cun"].ToString()) == 0)
            //    {
            //        Button5.Visible=true;
            //    }
            //    else
            //    { 
            //        Button5.Visible=false;
            //    }
                
            //}
            //dr.Close();
            //dr.Dispose();
            //db.Close();
        }
    }


    protected void Button1_Click(object sender, EventArgs e)
    {
        bool booltemp = false;
        Db db = new Db();
        System.Data.IDataReader dr;
        //<!--检查是否有投标记录
        StrSql = "Select Top 10 * From project_bid where projectid=" + CWProjectid;
        dr = db.DataReader(StrSql);
        if (dr.Read())
        {
            booltemp = false;
        }
        else
        {
            booltemp = true;
        }
        dr.Close();
        dr.Dispose();
        if (booltemp)
        {
            db.Close();
            Response.Write("<script>window.alert('对不起，没有投标数据，该功能不能使用');</script>");
            Response.Write("<scritp>window.location.href='Compete.aspx?" + CWProjectid + "'</script>");
            Response.End();
        }
        //检查是否有投标记录-->

        //<!--检查是否已经完成
        StrSql = "Select Top 10 * From v_competeprice Where Projectid=" + CWProjectid;
        dr = db.DataReader(StrSql);
        if (dr.Read())
        {
            dr.Close();
            dr.Dispose();
            Response.Write("<script>window.alert('第一步已经操作过了，请执行其他步骤！');</script>");
            Msg1.Text = "第一步已经操作过了";
        }
        else
        {
            dr.Close();
            dr.Dispose();
            //报价为零废标
            StrSql = " Update Project_bid Set CheckStatus='3',memo9='报价为零废标' where  checkstatus=1 and (smprice=0 or smprice is null) And ProjectId=" + CWProjectid.ToString();
            db.Command(StrSql);
            //解密未成功废标
            StrSql = "update Project_bid Set CheckStatus='3',memo9='解密未成功废标' where checkstatus=1 and  accountid  in (select accountid from  ProjectCompanyAttribution where BidSuccess=0 and  ProjectId=" + CWProjectid.ToString() + ") and ProjectId=" + CWProjectid.ToString();
            db.Command(StrSql);
            //高于限价的废标

            StrSql = " Update Project_bid Set CheckStatus='4',memo9='高于限价的废标' From goods b, LimitPriceTable as a" +
                     " Where a.Projectid=Project_bid.Projectid and project_bid.Goodsid=b.Goodsid and a.GoodsId=Project_bid.GoodsId and Convert(numeric(20,2),Project_bid.SPPrice)>Convert(numeric(20,2),a.LimitPrice*b.Factor)" +
                     " and LimitPrice>0 and Project_bid.ProjectId=" + CWProjectid.ToString() + " And Project_Bid.CheckStatus=1";
            db.Command(StrSql);


            //清除限价（0）、竞价（1）状态  不限不竞（2） 备案采购（3）
            StrSql = @"Update Project_bid Set Status=Null Where Status<>3 and Status<>2 And ProjectId=" + CWProjectid.ToString() + " ";
            db.Command(StrSql);
            
            //分出竞价和限价品种
            StrSql = "Update BidGoods_View Set Status=1 From ( " +
                   " select ProductCode,QualityLevels From dbo.BidGoods_View Where Status Is Null And CheckStatus=1 And ProjectId=" + CWProjectid.ToString() + " Group By ProductCode,QualityLevels " +
                   " having count(*)>2) a " +
                   " Where BidGoods_View.ProductCode=a.ProductCode " +
                   " And BidGoods_View.QualityLevels=a.QualityLevels And BidGoods_View.CheckStatus=1 And Status Is Null And BidGoods_View.ProjectId=" + CWProjectid.ToString();
            db.Command(StrSql);

            StrSql = "Update Project_bid Set Status=0 Where CheckStatus=1 And Status Is Null  And ProjectId=" + CWProjectid.ToString();
            db.Command(StrSql);
            
            //将竞价数据导入到竞价表中
            //StrSql = "Delete CompeteTable Where Bidid in (Select Bidid From Project_Bid Where Projectid=" + CWProjectid.ToString() + ")";//删除竞价表
            //db.Command(StrSql);

           // StrSql = "Insert into CompeteTable(BidId,CompeteNum) Select BidId,1 From Project_Bid Where Status=1 And CheckStatus=1 And ProjectId=" + CWProjectid.ToString() + " ";
            StrSql = "Insert into CompeteTable(BidId,CompeteNum) Select BidId,1 From Project_Bid Where Status=1 And CheckStatus=1 And bidid not in  (select bidid from CompeteTable where  ProjectId=" + CWProjectid.ToString() + ") and  ProjectId=" + CWProjectid.ToString() + " ";
            db.Command(StrSql);
            Response.Write("<script>window.alert('竞价数据导入成功！');</script>");
            
        }
        db.Close();
       
        
    }



    protected void Button4_Click(object sender, EventArgs e)//筛选
    {
        Db db = new Db();
        string[] sqlTrans = new string[13];
        sqlTrans[0] = "Update Project_Bid Set Checkstatus=1 Where Checkstatus=6 And Status=1";
        //将竞价数据导入临时表
        StrSql = "Select * Into comparetemp From v_competeprice Where projectid='" + CWProjectid.ToString() + "' And checkstatus='1' And Status=1";
        
        sqlTrans[1] = StrSql;
        //挑出最大排名数据，导入到排名表中
        sqlTrans[2] = "Select CompeteMinPrice,CompetePackPrice,a.bidid,a.ProductId,a.qualitylevels,a.ProductCode,paiming=(select Count(convert(numeric(20,4),CompeteMinPrice))+1 From comparetemp Where  comparetemp.ProductCode=a.ProductCode And comparetemp.Qualitylevels=a.Qualitylevels and IsNull(convert(numeric(20,4),a.CompeteMinPrice),0)>IsNull(convert(numeric(20,4),CompeteMinPrice),0) ) Into comparenum From comparetemp a order by ProductCode,Qualitylevels";
        //将组号为
        sqlTrans[3] = "Update project_bid set checkstatus='7' Where bidid in (Select bidid From comparenum,(select count(*) as mingci,ProductCode,Qualitylevels From comparenum Group by ProductCode,Qualitylevels having  count(*)=3) aa where comparenum.ProductCode=aa.ProductCode and comparenum.Qualitylevels=aa.Qualitylevels And comparenum.paiming>0 And comparenum.paiming<3)";//三家入围2家
        sqlTrans[4] = "Update project_bid set checkstatus='7' Where bidid in (Select bidid From comparenum,(select count(*) as mingci,ProductCode,Qualitylevels From comparenum Group by ProductCode,Qualitylevels having  count(*)>3 and  max(paiming)<6) aa where comparenum.ProductCode=aa.ProductCode and comparenum.Qualitylevels=aa.Qualitylevels And comparenum.paiming>0 And comparenum.paiming<4)";//4－5家入围3家
        sqlTrans[5] = "Update project_bid set checkstatus='7' Where bidid in (Select bidid From comparenum,(select count(*) as mingci,ProductCode,Qualitylevels From comparenum Group by ProductCode,Qualitylevels having  count(*)>5 and  max(paiming)<8) aa where comparenum.ProductCode=aa.ProductCode and comparenum.Qualitylevels=aa.Qualitylevels And comparenum.paiming>0 And comparenum.paiming<5)";//6－7家入围四家
        sqlTrans[6] = "Update project_bid set checkstatus='7' Where bidid in (Select bidid From comparenum,(select count(*) as mingci,ProductCode,Qualitylevels From comparenum Group by ProductCode,Qualitylevels having  count(*)>7 and  max(paiming)<10) aa where comparenum.ProductCode=aa.ProductCode and comparenum.Qualitylevels=aa.Qualitylevels And comparenum.paiming>0 And comparenum.paiming<6)";//8－9家入围五家
        sqlTrans[7] = "Update project_bid set checkstatus='7' Where bidid in (Select bidid From comparenum,(select count(*) as mingci,ProductCode,Qualitylevels From comparenum Group by ProductCode,Qualitylevels having  count(*)>10) aa where comparenum.ProductCode=aa.ProductCode and comparenum.Qualitylevels=aa.Qualitylevels And comparenum.paiming>0 And comparenum.paiming<7)";//大于10家入围六家
        sqlTrans[8] = "Update project_bid Set checkstatus='6' Where Status='1' And Checkstatus=1";//将竞价数据废标
        sqlTrans[9] = "Update project_bid Set checkstatus='1' Where CheckStatus='7' And Status='1'";//将竞价状态转为审核通过
        //删除临时表comparetemp
        sqlTrans[10] = "drop table comparetemp";
        //删除临时表comparenum
        sqlTrans[11] = "drop table comparenum";
        db.ExecuteTransaction(sqlTrans, 120);
        Response.Write("<script>window.alert('竞价统计结束！');</script>");
        db.Close();

        
    }

    public void Init1()
    {
        string CStatus="";
        Db db = new Db();
        DbPage dbPage = new DbPage(db);
        dbPage.Table = "v_competeprice,company,product ";                                               //表名，可以多个表关联
        dbPage.Key = "BidId";
        //该查询可以唯一表示记录的字段，该属性必须设置且只能有一个唯一标识字段
        dbPage.Where = " projectid=" + CWProjectid.ToString() + " and Status=1 And v_competeprice.productId=product.productid and v_competeprice.CheckStatus like '%" + CStatus + "%' and v_competeprice.accountid=company.accountid and (v_competeprice.checkstatus='1' or v_competeprice.checkstatus='6')";

        dbPage.SelectField = "BidId,CompanyName,V_competeprice.ProductCode,productname,Convert(numeric(20,4),competeminprice) As competeminprice,Convert(numeric(20,2),CompetePackPrice) As CompetePackPrice,medicinemodel,outlookc,factory,QualityLevels,v_competeprice.checkstatus ";//可以不设，默认为“*”

        dbPage.OrderBy = "V_competeprice.ProductCode,V_competeprice.QualityLevels,competeminprice,BidId";                                //排序字段，可以不输入。不输入时以 Key 作为排序字段

       // dbPage.PageIndex = SysFun.ToInt(Request.QueryString["PG"]);
        dbPage.PageIndex = SysFun.ToInt(Request["CurrentlyPageIndex"]);          //设置需要查询第几页
        dbPage.PageSize = 30;                                                   //设置每页显示多少条记录


        System.Text.StringBuilder sb = new System.Text.StringBuilder();
        System.Data.IDataReader dr = dbPage.ExecuteReader();

        sb.Append("<table border=1 cellpadding='0' cellspacing='0'   style='border-collapse: collapse' bordercolor='#111111' width='98%' height='128'>");
        sb.Append("<tr style='font-weight:bold'>");
        sb.Append("<td nowrap align=center>投标人</td>");
        sb.Append("<td nowrap align=center>通用名</td>");
        sb.Append("<td nowrap align=center>剂型</td>");
        sb.Append("<td nowrap align=center>规格</td>");
        sb.Append("<td align='center' nowrap>生产企业</td>");
        sb.Append("<td nowrap align=center>品目号</td>");
        sb.Append("<td nowrap align=center>质量层次</td>");
        sb.Append("<td align='center' nowrap>最小制剂报价</td>");
        sb.Append("<td align='center' nowrap>最小包装报价</td>");
        sb.Append("<td align='center' nowrap>竞价结果</td>");
        sb.Append("</tr>");

        int intNum = 0;
        string levels = "", results = "";

        while (dr.Read())
        {
            intNum++;
            levels = SysFun.ToTrim(dr["QualityLevels"]);
            switch (levels)
            {
                case "0":
                    levels = "普通GMP药品";
                    break;
                case "1":
                    levels = "专利药品";
                    break;
                case "2":
                    levels = "1999年以来获得国家科学技术奖的药品";
                    break;
                case "3":
                    levels = "国家一类新药";
                    break;
                case "4":
                    levels = "原研制药品";
                    break;
                case "5":
                    levels = "单独定价药品";
                    break;
                case "6":
                    levels = "优质优价中成药";
                    break;
                case "7":
                    levels = "全国医药工业百强企业的药品";
                    break;
                case "8":
                    levels = "中药保护品种";
                    break;
                case "9":
                    levels = "欧美认证药品";
                    break;
            }

            results = SysFun.ToTrim(dr["checkStatus"]);
            switch (results)
            {
                case "1":
                    results = "竞价成功";
                    break;
                case "6":
                    results = "竞价未入围";
                    break;
            }


            sb.Append("<tr>");
            sb.Append("<td>" + SysFun.ToTrim(dr["CompanyName"]) + "</td>");
            sb.Append("<td>" + SysFun.ToTrim(dr["productname"]) + "</td>");
            sb.Append("<td>" + SysFun.ToTrim(dr["medicinemodel"]) + "</td>");
            sb.Append("<td>" + SysFun.ToTrim(dr["outlookc"]) + "</td>");
            sb.Append("<td>" + SysFun.ToTrim(dr["factory"]) + "</td>");
            sb.Append("<td>" + SysFun.ToTrim(dr["ProductCode"]) + "</td>");
            sb.Append("<td>" + levels + "</td>");
            sb.Append("<td>" + SysFun.ToTrim(dr["competeminprice"]) + "</td>");
            sb.Append("<td>" + SysFun.ToTrim(dr["competePackPrice"]) + "</td>");
            sb.Append("<td>" + results + "</td>");
            sb.Append("</tr>");
        }


        sb.Append("</table>");
        Label1.Text = sb.ToString();
        this.DbPageFlip1.DbPage = dbPage;
        dbPage.Db.Close();
        dr.Close();
        dr.Dispose();
        db.Close();
       
    }
    protected void ButLimitPriceList_Click(object sender, EventArgs e)//限价废标的
    {
        Db db = new Db();
        System.Data.IDataReader dr;
        StrSql = "Select GoodsId AS 流水号,ProductCode AS 品目号,BidTableName AS 招标通用名,BidTableType AS 招标剂型,BidTableOutlookc AS 招标规格,ProductName AS 通用名,goodsname AS 商品名,Medicinemodel AS 剂型,Outlookc AS 规格,IsNull(Unit,'')+'/'+IsNull(PackUnit,'') As 包装,factor AS 转换系数,QualityLevels AS 质量层次,scname AS 生产企业,TBRNAME AS 投标人,TBUserCode AS 投标人注册号,FactoryUserCode AS 企业编码,Convert(Numeric(20,2),spprice) AS 报价,Convert(Numeric(20,4),smprice) AS 最小制剂价 From bidprice where checkstatus=4 And ProjectId="+ CWProjectid.ToString();
        dr = db.DataReader(StrSql);
        this.GridView1.DataSource = dr;
        this.GridView1.DataBind();
        db.Close();
        dr.Close();
        dr.Dispose();
        Print(1);

    }
    public void Print(int i)
    {
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "GB2312";
        // 添加头信息，为"文件下载/另存为"对话框指定默认文件名 
        if (i == 1)
        {
            Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("限价废标品种.xls"));
        }
        else if (i==2)
        {
            Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("竞价品种.xls"));
        }
        else if (i == 3)
        {
            Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("竞价前限价品种.xls"));
        }
        else if (i == 4)
        {
            Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("竞价后限价品种.xls"));
        }
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        Response.ContentType = "application/ms-excel";
        this.EnableViewState = false;
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
        this.GridView1.RenderControl(oHtmlTextWriter);

        // 把文件流发送到客户端 
        Response.Write(oStringWriter.ToString());
        // 停止页面的执行

        Response.End();
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
        //OverRide　为了使导出成Excel可行！
    }
    protected void ButCompeteList_Click(object sender, EventArgs e)//竞价的
    {
        Db db = new Db();
        System.Data.IDataReader dr;
        StrSql = "Select bidprice.Bidid,GoodsId AS 流水号,ProductCode AS 品目号,BidTableName AS 招标通用名,BidTableType AS 招标剂型,BidTableOutlookc AS 招标规格,ProductName AS 通用名,goodsname AS 商品名,Medicinemodel AS 剂型,Outlookc AS 规格,IsNull(Unit,'')+'/'+IsNull(PackUnit,'') As 包装,factor AS 转换系数,QualityLevels AS 质量层次,scname AS 生产企业,TBRNAME AS 投标人,TBUserCode AS 投标人注册号,FactoryUserCode AS 企业编码,Convert(Numeric(20,2),CompetePackPrice) AS 竞价报价,Convert(Numeric(20,4),CompeteMinPrice) AS 竞价最小价 From bidprice,CompeteTable where checkstatus=1 And Status=1 And ProjectId="+ CWProjectid.ToString() +" and bidprice.bidid=CompeteTable.bidid";
        dr = db.DataReader(StrSql);
        this.GridView1.DataSource = dr;
        this.GridView1.DataBind();
        db.Close();
        dr.Close();
        dr.Dispose();
        Print(2);

    }
    protected void ButYijiaList_Click(object sender, EventArgs e)//限价
    {
        Db db = new Db();
        System.Data.IDataReader dr;
        StrSql = "Select GoodsId AS 流水号,ProductCode AS 品目号,BidTableName AS 招标通用名,BidTableType AS 招标剂型,BidTableOutlookc AS 招标规格,ProductName AS 通用名,goodsname AS 商品名,Medicinemodel AS 剂型,Outlookc AS 规格,IsNull(Unit,'')+'/'+IsNull(PackUnit,'') As 包装,factor AS 转换系数,QualityLevels AS 质量层次,scname AS 生产企业,TBRNAME AS 投标人,TBUserCode AS 投标人注册号,FactoryUserCode AS 企业编码,Convert(Numeric(20,2),spprice) AS 报价,Convert(Numeric(20,4),smprice) AS 最小制剂价 From bidprice where checkstatus=1 And Status=0 And ProjectId=" + CWProjectid.ToString();
        dr = db.DataReader(StrSql);
        this.GridView1.DataSource = dr;
        this.GridView1.DataBind();
        db.Close();
        dr.Close();
        dr.Dispose();
        Print(3);

    }
    protected void Butjjyj_Click(object sender, EventArgs e)//竞价后限价的
    {
        Db db = new Db();
        System.Data.IDataReader dr;
        StrSql = "Select GoodsId AS 流水号,ProductCode AS 品目号,BidTableName AS 招标通用名,BidTableType AS 招标剂型,BidTableOutlookc AS 招标规格,ProductName AS 通用名,goodsname AS 商品名,Medicinemodel AS 剂型,Outlookc AS 规格,IsNull(Unit,'')+'/'+IsNull(PackUnit,'') As 包装,factor AS 转换系数,QualityLevels AS 质量层次,scname AS 生产企业,TBRNAME AS 投标人,TBUserCode AS 投标人注册号,FactoryUserCode AS 企业编码,Convert(Numeric(20,2),spprice) AS 报价,Convert(Numeric(20,4),smprice) AS 最小制剂价 From bidprice where checkstatus=1 And Status=0 And ProjectId=" + CWProjectid.ToString() +" And BidId in (Select BidId From ComPeteTable)";
        dr = db.DataReader(StrSql);
        this.GridView1.DataSource = dr;
        this.GridView1.DataBind();
        db.Close();
        dr.Close();
        dr.Dispose();
        Print(4);

    }
}
